﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelAutomationRunner
{

    public class WorksheetsToWorkbook : ExcelApplicationBase
    {

        private string _directory;
        private string _errorMessage;
        private List<string> _worksheetFiles;
        private bool _deleteWorksheetFiles;

        /// <summary>
        /// Creates excel file containing a workbook containing the contents of each single file represented by the paths passed in worksheetFiles.
        /// The worksheets are named according to the file names of the individual files (less extension).
        /// </summary>
        /// <param name="worksheetFiles">Expected: * Each string is path to file with one worksheet.
        ///                                        * Each file is in the same dir.
        ///                                        * Each file has extension .xlsx
        ///                                        * We have read/write access to that dir.</param>
        /// <returns>Blank/Null on success; error message on failure (return apparently can't be nullable type)</returns>
        public Tuple<bool, string> ToWorkbook( List<string> worksheetFiles, string newWorkbookFilePath, bool deleteWorksheetFiles = true )
        {
            _worksheetFiles = worksheetFiles;
            _newPath = newWorkbookFilePath;
            _deleteWorksheetFiles = deleteWorksheetFiles;
            return ProcessApplication();  // this calls Process below.
        }

        protected override Tuple<bool, string> Process( Application excel )
        {
            if ( FilesValid() )
            {
                _newWorkbook = excel.Workbooks.Add();
                //_newWorkbook = _excel.ActiveWorkbook;  // nada; this is for VBA stuff.

                //_newWorkbook.Worksheets.Delete();  // Nothing there yet; see ClearDefaultWorksheets()
                _worksheetFiles.ForEach( f => AddWorksheet( excel, f ) );
                DeleteDefaultWorksheets();
                CloseWorkbook();
                DeleteWorksheetFiles();
                return new Tuple<bool, string>( true, string.Empty );
            }
            else
            {
                return new Tuple<bool, string>( false, _errorMessage );
            }
        }

        private void CloseWorkbook()
        {
            _newWorkbook.SaveAs( _newPath );
            _newWorkbook.Close();  // If you forget this, you'll get the "Document Recovery" pane when you next open the file.
        }

        /// <summary>
        /// The 3 worksheets that are created by default don't seem to be created up front (not present in AddWorsheet() ).
        /// The behavior seems to be that, as I create new sheets, one less of these sheets appears in the resulting file.
        /// So if I create 2 sheets, one of them remains.
        /// </summary>
        private void DeleteDefaultWorksheets()
        {
            /// _newWorkbook.Worksheets["name"]  // if name doesn't exist, it doesn't return null, it friggin blows up.
            foreach ( Worksheet worksheet in _newWorkbook.Worksheets )
            {
                if ( Constants.DEFAULT_WORKSHEETS.Contains( worksheet.Name ) )
                {
                    worksheet.Delete();
                    ReleaseComObject( worksheet );
                }
            }

        }

        private void DeleteWorksheetFiles()
        {
            if ( _deleteWorksheetFiles )
            {
                _worksheetFiles.ForEach( f => File.Delete( f ) );
            }
        }

        private void AddWorksheet( Application excel, string inputFilePath )
        {
            var sheetName = GetSheetName( inputFilePath );
            /// There are no sheets present at this time.  Upon saving, if I've added 2 sheets, there will be another "Sheet1."  Squirrely bastard.
            /// So it seems the minimum 3 default sheets get created at that time if necessary?
            Worksheet newSheet = _newWorkbook.Worksheets.Add();
            CopyExistingSheet( excel, inputFilePath, newSheet );
            newSheet.Name = sheetName;
        }

        private static string GetSheetName( string inputFilePath )
        {
            return Path.GetFileName( inputFilePath ).Replace( Constants.EXCEL_EXTENSION, string.Empty );
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="inputFilePath">Has been validated</param>
        /// <param name="newSheet"></param>
        private static void CopyExistingSheet( Application excel, string inputFilePath, Worksheet newSheet )
        {
            var existingWorkbook = excel.Workbooks.Open( inputFilePath );
            Worksheet existingWorksheet = existingWorkbook.Worksheets[1];
            if ( existingWorksheet != null )
            {
                try
                {
                    // How do you paste?  By calling copy of course.
                    existingWorksheet.UsedRange.Copy( newSheet.Range[Constants.TOP_LEFT_CELL] );
                    /// Some of the crap I tried that doesn't work...
                    //existingWorksheet.UsedRange.Select();
                    //existingWorksheet.Copy();
                    //newSheet.Range[Constants.TOP_LEFT_CELL].Select();  bombs
                    //newSheet.Paste();
                    //newSheet.UsedRange.Cells.PasteSpecial();
                }
                finally
                {
                    existingWorkbook.Close();
                    ReleaseComObject( existingWorkbook );
                }
            }
        }

        private bool FilesValid()
        {
            _directory = Path.GetDirectoryName( _worksheetFiles.FirstOrDefault() );
            if ( !Directory.Exists( _directory ) )
            {
                _errorMessage = string.Format( "Invalid path: {0}", _worksheetFiles.FirstOrDefault() );
                return false;
            }

            for ( int i = 0; i < _worksheetFiles.Count; i++ )
            {
                if ( Constants.DEFAULT_WORKSHEETS.Contains( GetSheetName( _worksheetFiles[i] ) ) )
                {
                    // Reason: see ClearDefaultWorksheets()
                    _errorMessage = string.Format( "Invalid input file name; cannot be same as Excel default new worksheet names: {0}", _worksheetFiles[i] );
                    return false;
                }
                if ( Path.GetExtension( _worksheetFiles[i] ) != Constants.EXCEL_EXTENSION )
                {
                    _errorMessage = string.Format( "Invalid extension: {0}", _worksheetFiles[i] );
                    return false;
                }
                if ( Path.GetDirectoryName( _worksheetFiles[i] ) != _directory )
                {
                    _errorMessage = "All paths expected in same directory";
                    return false;
                }
                if ( !File.Exists( _worksheetFiles[i] ) )
                {
                    _errorMessage = string.Format( "Invalid path: {0}", _worksheetFiles[i] );
                    return false;
                }
            }
            return true;
        }

    }
}
